Excel BI - Excel Challenge 828

excel-challenges

excel-formulas

🔰 Answer Expected Numbers Group1 Group2 Group3 Group4 Group5 A group finishes when a number is consecutive.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 828

Challenge Description

🔰 Answer Expected Numbers Group1 Group2 Group3 Group4 Group5 A group finishes when a number is consecutive. A group starts when consecutive numbers are not encountered. Vertically align the groups. Also get the group names dynamically. Ex. 2, 1, 2, 2, 3, 4, 4, 4, 4 => Group1 : 2, 1, 2 and Group2: 3, 4

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/828/828 Group By.xlsx"
input = read_excel(path, range = "A2:A21")
test  = read_excel(path, range = "B2:F6")

result = input %>%
  mutate(cid = consecutive_id(Numbers)) %>%
  mutate(auxn = row_number(), .by = cid) %>%
  mutate(group = cumsum(auxn == 2) + 1) %>%
  filter(auxn == 1) %>%
  select(-c(cid, auxn)) %>%
  mutate(rn = row_number(), .by = group) %>%
  pivot_wider(names_from = group, values_from = Numbers, names_prefix = "Group") %>%
  select(-rn)

identical(result, test)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Reshape the result into the workbook output format.
Strengths: The reshaping step mirrors the workbook output closely instead of forcing extra post-processing.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The last reshape turns a raw transformation into something that already looks like a report.

import pandas as pd

path = "800-899/828/828 Group By.xlsx"

input = pd.read_excel(path, usecols="A", skiprows=1, nrows=20)
test  = pd.read_excel(path, usecols="B:F", skiprows=1, nrows=4)

s = input["Numbers"]
cid = s.ne(s.shift()).cumsum()
auxn = s.groupby(cid).cumcount().add(1)
group = auxn.eq(2).cumsum().add(1)

df2 = input.loc[auxn.eq(1)].assign(group=group[auxn.eq(1)])
df2["rn"] = df2.groupby("group").cumcount().add(1)

wide = (df2.pivot(index="rn", columns="group", values="Numbers")
    .rename(columns=lambda c: f"Group{int(c)}")
    .reset_index(drop=True)
    .astype({f"Group{i+1}": dt for i, dt in enumerate(test.dtypes)}, errors="ignore"))

print(wide.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.